﻿Imports System.Data.SqlClient
Imports System.IO
Imports System.Data.OleDb
Imports System.Text.RegularExpressions
Imports System.DirectoryServices


Module ModuloOperaciones

    Function CreateFileRepository(ByVal strNameRepository As String, ByVal strPathFileIni As String, ByVal str_PathDocument As String, _
                                  ByVal intDBSelectedACreate As Int16, ByVal strPathNewBD As String, ByVal strTextBDSelected As String, _
                                        Optional ByVal bolIsNewBdSelected As Boolean = Nothing) As Boolean

        Dim strPathCompleteFileIni As String = strPathFileIni & "\" & strNameRepository & g_StrRepositoryFileExtension
        Dim strPathCompleteDirectory As String = str_PathDocument & "\" & strNameRepository

        Dim strImgDirectory As String = "Img" & strNameRepository '& "\"
        Dim conecctionString As String = ""

        If Not ExistFile(strPathCompleteFileIni) Then
            createFile(strPathCompleteFileIni)
        Else
            Throw New Exception("El archivo " & strPathCompleteFileIni & " ya existe." & vbCrLf & "Por favor ingrese otro nombre de archivo.")
            Exit Function
        End If
        If Not ExistDirectory(strPathCompleteDirectory) Then
            createDirectory(strPathCompleteDirectory)
            createDirectory(strPathCompleteDirectory & "\" & strImgDirectory)
        Else
            Throw New Exception("La carpeta " & strPathCompleteDirectory & " ya existe." & vbCrLf & "Por favor ingrese otro nombre para la carpeta.")
            Exit Function
        End If

        If EscribeValoresDb(strNameRepository, strPathCompleteFileIni, strPathNewBD, _
                 strImgDirectory, intDBSelectedACreate) Then
            If intDBSelectedACreate = TypeBD.ACCESS Then
                Dim strPathCompleteDestineFileBd As String = strPathCompleteDirectory & "\" & strNameRepository & ".mdb"
                Call copyTemplateBdToPathDefined(strPathCompleteDestineFileBd)
            ElseIf intDBSelectedACreate = TypeBD.SQL Then
                If Not bolIsNewBdSelected Then
                    Call CreaBaseSQL(strTextBDSelected, strPathNewBD, False, intDBSelectedACreate)
                Else
                    Call CreaBaseSQL(strNameRepository, strPathNewBD, True, intDBSelectedACreate)
                End If
            ElseIf intDBSelectedACreate = TypeBD.ORACLE Then
                Call CreaBaseSQL(strTextBDSelected, strPathNewBD, False, intDBSelectedACreate)
            End If
        End If
        CreateFileRepository = True
    End Function


    Sub CopyTemplateBdToPathDefined(ByVal strPathFileBdDestine As String)
        IO.File.Copy(g_AppPath, strPathFileBdDestine, True)
    End Sub
    Function CreaBaseSQL(ByVal StrNameDb As String, ByVal StrConecctionString As String, Optional ByVal BolCreaDb As Boolean = True, _
                    Optional ByVal TipoBaseDatos As Int16 = 0) As Boolean

        If TipoBaseDatos = 1 Then 'sqldatabase Then

            Dim myConn As OleDbConnection = New OleDbConnection(StrConecctionString) '"Server=localhost;Integrated security=SSPI;database=master")
            Dim str As String = "CREATE DATABASE " & StrNameDb
            Dim path As String = Directory.GetParent(Directory.GetParent(Directory.GetCurrentDirectory).ToString).ToString


            If ExistFile(path & g_PathTemplatesBD & "SQL.script") Then
                If Not BolCreaDb Then
                    ExecuteSQL(StrConecctionString.Replace(StrNameDb, "MASTER"), str)
                End If
                ExecuteScriptSQL(myConn, path & g_PathTemplatesBD & "SQL.script")
            End If
        ElseIf TipoBaseDatos = 2 Then 'TODO: Oracle

        End If

    End Function



    Function CreateFile(ByVal strPath As String) As Boolean
        Dim bolFlag As Boolean = False
        Try
            Dim fs As FileStream = File.Create(strPath)
            fs.Close()
            Return Not bolFlag
        Catch ex As Exception
            Return bolFlag
        End Try

        Return bolFlag
    End Function

    Function createDirectory(ByVal strPath As String) As Boolean
        Dim bolFlag As Boolean = False
        Try
            Directory.CreateDirectory(strPath)
            Return Not bolFlag
        Catch ex As Exception
            Return bolFlag
        End Try

        Return bolFlag
    End Function

    Public Function EscribeValoresDb(ByVal strFileName As String, ByVal StrPathFile As String, _
                                     ByVal strPathNewBD As String, ByVal strImgDirectory As String, _
                                     ByVal intDBSelectedACreate As Int16) As Boolean

        Dim Contents As String = ""
        Dim bAns As Boolean = False
        Dim objReader As StreamWriter
        Try
            objReader = New StreamWriter(StrPathFile)
            objReader.Write("Titulo=" & strFileName)
            objReader.WriteLine()
            If intDBSelectedACreate = TypeBD.ACCESS Then 'AccessDataBase
                objReader.Write("Ruta=" & strPathNewBD & ".mdb")
                objReader.WriteLine()
                objReader.Write("imagenes=" & strImgDirectory)
                objReader.WriteLine()
                objReader.Write("Provider=" & g_StrProviderAccessDB)
            Else
                objReader.Write("Ruta=" & (strPathNewBD))
                objReader.WriteLine()
                objReader.Write("imagenes=" & strImgDirectory)
                objReader.WriteLine()
                If intDBSelectedACreate = TypeBD.SQL Then 'sqldatabase
                    objReader.Write("Provider=" & g_StrProviderSQLDB)
                Else 'Oracle
                    objReader.Write("Provider=" & g_StrProviderOracleDB)
                End If
            End If
            objReader.Close()
            bAns = True
        Catch Ex As Exception
            Ex.ToString()

        End Try
        Return bAns
    End Function

    Function GetLisDataBasesSQL(ByVal srtCnx As String) As DataTable
        Dim bolResulTest As Boolean = False
        Try

            Dim OleCnx As New OleDbConnection(srtCnx)
            OleCnx.Open()

            Dim OleCom As New OleDbCommand
            OleCom.Connection = OleCnx
            OleCom.CommandType = CommandType.StoredProcedure
            OleCom.CommandText = "sp_databases"
            Dim table As New DataTable
            Dim OleDA As New OleDbDataAdapter(OleCom)
            OleDA.Fill(table)

            Return table
        Catch ex As Exception
            g_StrMessageBoxDialog = ex.ToString
            Return Nothing
            'Throw New Exception(g_StrMessageBoxDialog)
        End Try

    End Function

    Public Function TestDb(ByVal SrtCn As String, ByVal BolShowMensaje As Boolean) As Boolean


        Dim bolResulTest As Boolean = False
        Try
            Dim cntest As New OleDbConnection(SrtCn)
            cntest.Open()

            If cntest.State = ConnectionState.Open Then
                cntest.Close()
                cntest = Nothing
                bolResulTest = True
                g_StrMessageBoxDialog = "La conexion con el servidor se realizo correctamente."
                Return bolResulTest
                'Exit Function
            End If
        Catch ex As Exception
            g_StrMessageBoxDialog = ex.Message
            Return bolResulTest
        End Try

    End Function

    Public Sub ExecuteScriptSQL(ByVal connection As OleDbConnection, ByVal sqlFile As String)
        Dim sql As String = ""

        Using strm As FileStream = File.OpenRead(sqlFile)
            Dim reader As New StreamReader(strm)
            sql = reader.ReadToEnd()
        End Using


        Dim regex As New System.Text.RegularExpressions.Regex("^GO@", RegexOptions.IgnoreCase Or RegexOptions.Multiline)

        Dim lines As String() = regex.Split(sql)
        connection.Open()
        Dim transaction As OleDbTransaction = connection.BeginTransaction
        Using cmd As OleDbCommand = connection.CreateCommand()
            cmd.Connection = connection
            cmd.Transaction = transaction

            For Each line As String In lines
                If line.Length > 0 Then
                    cmd.CommandText = line
                    cmd.CommandType = CommandType.Text

                    Try
                        cmd.ExecuteNonQuery()
                    Catch generatedExceptionName As SqlException
                        transaction.Rollback()
                        connection.Close()
                        Throw
                    End Try
                End If
            Next
        End Using

        transaction.Commit()
        connection.Close()
    End Sub

    Public Sub ExecuteSQL(ByVal stringconnection As String, ByVal strSQL As String)
        Dim myConn As New OleDbConnection(stringconnection)
        Dim myCommand As New OleDbCommand(strSQL, myConn)
        Try
            myConn.Open()
            myCommand.ExecuteNonQuery()
            'MessageBox.Show("DataBase is Created Successfully", "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Catch ex As System.Exception
            'MessageBox.Show(ex.ToString(), "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Finally
            If myConn.State = ConnectionState.Open Then
                myConn.Close()
            End If
        End Try
    End Sub

    Public Function MessageMakeAction(ByVal p_Mensaje As String) As Boolean

        If g_FrmMessageBox Is Nothing Then
            g_FrmMessageBox = New frmMessageBoxAction
        End If
        g_FrmMessageBox.btnOK.Focus()
        g_FrmMessageBox.Text = g_ApplicationName
        g_FrmMessageBox.txtMensaje.Text = p_Mensaje
        g_FrmMessageBox.ShowDialog()

        Return g_bol_IsReady

    End Function

    Public Function GetAllUsersGroupsDomain(Optional ByVal _nameDomain As String = "", Optional ByVal _userDomain As String = "", Optional ByVal _pwdDomain As String = "") As Hashtable
        Dim searchRoot As DirectoryEntry

        If g_bol_ExistDomain Then
            searchRoot = New DirectoryEntry("LDAP://" & Environment.UserDomainName)
        Else
            searchRoot = New DirectoryEntry("LDAP://" & _nameDomain, _userDomain, _pwdDomain)
        End If
        '

        Dim search As New DirectorySearcher(searchRoot)
        Dim RetArray As New Hashtable()
        Try
            search.Filter = "(|(objectClass=user)(objectCategory=person)(objectCategory=Group))" 'PARA USUARIOS DE DOMINIO
            'search.Filter = "(&(objectClass=group)(objectCategory=Group))" 'PARA GRUPOS DE DOMINIO
            search.PropertiesToLoad.Add("samaccountname")
            search.PropertiesToLoad.Add("DisplayName")

            Dim result As SearchResult
            Dim resultCol As SearchResultCollection = search.FindAll()
            If resultCol IsNot Nothing Then
                For counter As Integer = 0 To resultCol.Count - 1
                    result = resultCol(counter)
                    'If result.Properties.Contains("samaccountname") And result.Properties.Contains("DisplayName") Then
                    If result.Properties.Contains("samaccountname") Then
                        'RetArray.Add(DirectCast(result.Properties("samaccountname")(0), [String]), DirectCast(result.Properties("DisplayName")(0), [String]))
                        RetArray.Add(DirectCast(result.Properties("samaccountname")(0), [String]), DirectCast(result.Properties("samaccountname")(0), [String]))
                    End If
                Next
            End If
            Return RetArray
        Catch ex As Exception
            MessageMistake("El Dominio y/o usuario son Incorrectos")
            GetAllUsersGroupsDomain = Nothing

        End Try

    End Function

    'Public Function GetAllADDomainUsers(ByVal _nameDomain As String, ByVal _userDomain As String, ByVal _pwdDomain As String) As Boolean

    '    Dim searchRoot As New DirectoryEntry("LDAP://" & _nameDomain, _userDomain, _pwdDomain)
    '    'Dim searchRoot As New DirectoryEntry("LDAP://" & Environment.MachineName, "administrador", "agunsa21")
    '    Dim search As New DirectorySearcher(searchRoot)

    '    Try
    '        search.Filter = "(&(objectClass=user)(objectCategory=person))"
    '        search.PropertiesToLoad.Add("samaccountname")
    '        search.PropertiesToLoad.Add("DisplayName")

    '        'Dim result As SearchResult
    '        Dim resultCol As SearchResultCollection = search.FindAll()
    '        If resultCol IsNot Nothing Then
    '            g_bol_ExistDomain = True
    '        End If

    '        Return g_bol_ExistDomain
    '    Catch ex As Exception
    '        g_bol_ExistDomain = False
    '        Return g_bol_ExistDomain
    '    End Try

    'End Function

    Public Function IsFromDomain() As Boolean

        Dim searchRoot As New DirectoryEntry("LDAP://" & Environment.UserDomainName) 'nameDomain
        'Dim searchRoot As New DirectoryEntry("LDAP://" & Environment.MachineName, "administrador", "agunsa21")
        Dim search As New DirectorySearcher(searchRoot)

        Try
            search.Filter = "(&(objectClass=user)(objectCategory=person))"
            search.PropertiesToLoad.Add("samaccountname")
            search.PropertiesToLoad.Add("DisplayName")

            'Dim result As SearchResult
            Dim resultCol As SearchResultCollection = search.FindAll()
            If resultCol IsNot Nothing Then
                g_bol_ExistDomain = True
            End If

            Return g_bol_ExistDomain
        Catch ex As Exception
            g_bol_ExistDomain = False
            Return g_bol_ExistDomain
        End Try

    End Function

    Public Sub SendUpdateDataUser(ByVal strLogin As String, _
                                  ByVal strTipoAccessoDes As String, _
                                  ByVal strEstadoDes As String, _
                                  ByVal strNombre As String, _
                                  ByVal strEmail As String, _
                                  ByVal strIdUsuario As String, _
                                  ByVal strTipoAcceso As String, _
                                  ByVal strEstado As String, _
                                  ByVal strPwd As String, _
                                  ByVal bolBelongsToDomain As Boolean)

        'Dim _frmUsersAdd As frmUsersAdd
        '_frmUsersAdd = New frmUsersAdd
        '_frmUsersAdd.txtNameComplete.Text = strNombre
        '_frmUsersAdd.txtEmail.Text = strEmail
        '_frmUsersAdd.txtLogin.Text = strLogin
        '_frmUsersAdd.txtLogin.Enabled = False
        '_frmUsersAdd.rdbScanflow.Enabled = False
        '_frmUsersAdd.rdbWindows.Enabled = False
        '_frmUsersAdd.btnFindDomain.Enabled = False

        'If strTipoAcceso = "S" Then
        '    _frmUsersAdd.rdbScanflow.Checked = True
        '    _frmUsersAdd.txtPassword.Text = strPwd
        '    _frmUsersAdd.txtConfirmPassword.Text = strPwd
        'ElseIf strEstado = "W" Then
        '    _frmUsersAdd.txtPassword.Enabled = False
        '    _frmUsersAdd.txtConfirmPassword.Enabled = False
        '    _frmUsersAdd.rdbWindows.Checked = False
        'End If
        'If strEstado = "0" Then
        '    _frmUsersAdd.chkEnable.Checked = True
        'ElseIf strEstado = "1" Then
        '    _frmUsersAdd.chkEnable.Checked = False
        'End If

        '_frmUsersAdd._bolUpdateUser = True
        '_frmUsersAdd._IdUserUpdate = strIdUsuario
        '_frmUsersAdd.ShowDialog()
    End Sub

    Public Function ExisteArchivo(ByVal RutaFile As String) As Boolean
        If File.Exists(RutaFile) Then Return True
    End Function




End Module
